﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EFMagic.Schema;
using System.Data.SqlClient;
namespace EFMagic
{
    public class EFMSQLSchemaGenerator
    {
        EFMSchema newSchema, oldSchema;

        public EFMSQLSchemaGenerator(EFMSchema newSchema, EFMSchema oldSchema)
        {
            this.newSchema = newSchema;
            this.oldSchema = oldSchema;
        }

        public string DropTables()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in oldSchema.Tables
                         where !newSchema.Tables.ContainsKey(x.Key)
                         select x;
            foreach (var table in tables)
            {
                sb.AppendFormat("DROP TABLE {0}\n", table.Value.DBName);
            }
            return sb.ToString();
        }

        public string RenameTables()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         where oldSchema.Tables.ContainsKey(x.Key) &&
                                oldSchema.Tables[x.Key].DBName != x.Value.DBName
                         select x;
            foreach (var table in tables)
            {
                sb.AppendFormat("EXEC sp_rename '{0}', '{1}'\n", oldSchema.Tables[table.Key].DBName, table.Value.DBName);
            }
            return sb.ToString();
        }

        public string DropColumns()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         where oldSchema.Tables.ContainsKey(x.Key)
                         select x;
            foreach (var table in tables)
            {
                var columns = from x in oldSchema.Tables[table.Key].Columns
                              where !table.Value.Columns.ContainsKey(x.Key)
                              select x;
                foreach (var column in columns)
                {
                    sb.AppendFormat("ALTER TABLE {0} DROP COLUMN {1}\n", table.Value.DBName, column.Value.DBName);
                }
            }
            return sb.ToString();
        }

        public string RenameColumns()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         where oldSchema.Tables.ContainsKey(x.Key)
                         select x;
            foreach (var table in tables)
            {
                var columns = from x in table.Value.Columns
                              where oldSchema.Tables[table.Key].Columns.ContainsKey(x.Key) &&
                                    oldSchema.Tables[table.Key].Columns[x.Key].DBName != x.Value.DBName
                              select x;
                foreach (var column in columns)
                {
                    sb.AppendFormat("EXEC sp_rename '{0}.{1}', '{2}', 'COLUMN'\n",
                        table.Value.Name, oldSchema.Tables[table.Key].Columns[column.Key].DBName, column.Value.DBName);

                }
            }
            return sb.ToString();
        }

        public string NewColumns()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         where oldSchema.Tables.ContainsKey(x.Key)
                         select x;
            foreach (var table in tables)
            {
                var columns = from x in table.Value.Columns
                              where !oldSchema.Tables[table.Key].Columns.ContainsKey(x.Key)
                              select x;
                foreach (var column in columns)
                {
                    sb.AppendFormat("ALTER TABLE {0} ADD {1} {2} {3}\n", table.Value.DBName, column.Value.DBName,
                        column.Value.DBDataType, column.Value.AdditionalParameters);

                }
            }
            return sb.ToString();
        }

        public string NewTables()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         where !oldSchema.Tables.ContainsKey(x.Key)
                         select x;
            foreach (var table in tables)
            {
                sb.AppendFormat("CREATE TABLE {0} (\n", table.Value.DBName);
                foreach (var column in table.Value.Columns)
                {
                    sb.AppendFormat("{0} {1} {2},", column.Value.DBName, column.Value.DBDataType, column.Value.AdditionalParameters);
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")\n");
            }
            return sb.ToString();
        }

        public string DropIndexes()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in oldSchema.Tables
                         select x;
            foreach (var table in tables)
            {
                foreach (var ind in table.Value.Indexes)
                {
                    sb.AppendFormat("DROP INDEX {0}.{1}\n", table.Value.DBName, ind.Key);
                }
            }
            return sb.ToString();
        }

        public string DropConstraints()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in oldSchema.Tables
                         select x;
            foreach (var table in tables)
            {
                foreach (var constraint in table.Value.Constraints)
                {
                    sb.AppendFormat("ALTER TABLE {0} DROP CONSTRAINT {1}\n", table.Value.DBName, constraint.Value.Name);
                }
            }
            return sb.ToString();
        }

        public string CreateIndexes()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         select x;
            foreach (var table in tables)
            {
                foreach (var ind in table.Value.Indexes)
                {
                    sb.AppendFormat("CREATE INDEX {0} ON {1} ({2})\n", ind.Key, table.Value.DBName, table.Value.Columns[ind.Value.ColumnId].DBName);
                }
            }
            return sb.ToString();
        }

        public string AddConstraints()
        {
            StringBuilder sb = new StringBuilder();
            var tables = from x in newSchema.Tables
                         select x;

            foreach (var table in tables)
            {
                foreach (var pm in from x in table.Value.Constraints where x.Value is EFMPrimaryKey select x)
                {
                    sb.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2}) \n", table.Value.DBName, pm.Key, table.Value.Columns[pm.Value.ColumnId].DBName);
                }
                foreach (var un in from x in table.Value.Constraints where x.Value is EFMUnique select x)
                {
                    sb.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE ({2}) \n", table.Value.DBName, un.Key, table.Value.Columns[un.Value.ColumnId].DBName);
                }
                foreach (var rel in from x in table.Value.Constraints where x.Value is EFMForeignKey select x)
                {
                    sb.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4})\n",
                                    table.Value.DBName, rel.Value.Name, table.Value.Columns[rel.Value.ColumnId].DBName, newSchema.Tables[(rel.Value as EFMForeignKey).RTableId].DBName,
                                    newSchema.Tables[(rel.Value as EFMForeignKey).RTableId].Columns[(rel.Value as EFMForeignKey).RColumnId].DBName);
                }
            }
            return sb.ToString();

        }

        public string DropViews()
        {
            StringBuilder sb = new StringBuilder();
            var views = from x in oldSchema.Views
                        select x;
            foreach (var view in views)
            {
                sb.AppendFormat("DROP VIEW {0}\n", view.Value.Name);
            }
            return sb.ToString();
        }

        public string CreateViews()
        {
            StringBuilder sb = new StringBuilder();
            var views = from x in newSchema.Views
                        select x;
            foreach (var view in views)
            {
                sb.AppendFormat("CREATE VIEW [{0}] AS\n {1}\n", view.Value.Name, view.Value.SQL);
            }
            return sb.ToString();
        }

        public void UpdateDatabase(string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("", conn);
                cmd.CommandText = "BEGIN TRANSACTION\n";
                cmd.CommandText += DropTables();
                cmd.CommandText += RenameTables();
                cmd.CommandText += DropColumns();
                cmd.CommandText += RenameColumns();
                cmd.CommandText += NewColumns();
                cmd.CommandText += NewTables();
                cmd.CommandText += DropIndexes();
                cmd.CommandText += DropConstraints();
                cmd.CommandText += CreateIndexes();
                cmd.CommandText += AddConstraints();
                cmd.CommandText += DropViews();
                cmd.CommandText += "COMMIT\n";
                cmd.ExecuteNonQuery();
                cmd.CommandText = CreateViews();
                if (cmd.CommandText != "")
                {
                    cmd.CommandText = "BEGIN TRANSACTION\n" + cmd.CommandText + "COMMIT\n";
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }
    }
}
